UNION/* Long Term Schedule Fees */
SELECT por.description portfolio,
       TO_CHAR(cf.value_date, :default_date_format) value_date,
       cf.value_date value_date_date,
       ct.description type,
       cur.code ccy,
       cf.cur_uid con_cur,
       caft.code_description cf_type,
       to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
       TO_CHAR(lts.maturity_date, :default_date_format) maturity,
       ltc.description loan_title,
       NVL(cf.amount, 0) amount,
       NVL(cf.base_amount, 0) base_amount,
       DECODE(cf.status,
              '1', 'Unconfirmed',
              '2', 'Confirmed',
              'Changed') status,
       cf.cf_uid,
       org_cparty.name cparty_name,
       fol.description fol_desc
  FROM organisations org,
       organisation_roles or1,
       organisations org_cparty,
       organisation_roles orr_cpy,
       currencies cur,
       contract_types ct,
       portfolios por,
       long_term_contracts ltc,
       long_term_schedules lts,
       cash_flow_types caft,
       fee_schedules fs,
       cash_flows cf,
       star_site_parameters ssp,
       folders fol
 WHERE 1 = 1
   AND ssp.current_site = 'Y'
   AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
 --  AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
   AND NOT cf.status IN ('5', '9')
   AND fs.fs_uid = cf.fs_uid
   AND lts.lts_uid = fs.lts_uid
   AND ltc.ltc_uid = lts.ltc_uid
   AND por.por_uid = ltc.por_uid
   AND ct.ct_uid = ltc.ct_uid  
   AND caft.caft_uid = cf.caft_uid
   AND cur.cur_uid = cf.cur_uid
   AND or1.or_uid = ltc.or_uid_beneficiary
   AND org.org_uid = or1.org_uid
   AND orr_cpy.or_uid = ltc.or_uid_cparty
   AND org_cparty.org_uid = orr_cpy.org_uid
   AND por.portfolio_type = 'A'
   AND lts.details_complete = DECODE(:details_complete,
                                     'Y', :details_complete,
                                     lts.details_complete)
   AND por.contingent = DECODE(:contingent_liabilities,
                               'Y', por.contingent,
                               'N')
   AND ltc.domestic = DECODE(:domestic,
                               'Y', 'Y',
                               'N', 'N',
                               ltc.domestic) 
   AND ltc.fol_uid = fol.fol_uid(+) 
  AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date))  <= :exclude_if_contr_after )
   &lt_where_clause
UNION/* Long Term Contract Fees */
SELECT por.description portfolio,
       TO_CHAR(cf.value_date, :default_date_format) value_date,
       cf.value_date value_date_date,
       ct.description type,
       cur.code ccy,
       cf.cur_uid con_cur,
       caft.code_description cf_type,
--       to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
       to_char(ltc.ltc_uid) contract_no,
       TO_CHAR(ltc.maturity_date, :default_date_format) maturity,
       ltc.description loan_title,
       NVL(cf.amount, 0) amount,
       NVL(cf.base_amount, 0) base_amount,
       DECODE(cf.status,
              '1', 'Unconfirmed',
              '2', 'Confirmed',
              'Changed') status,
       cf.cf_uid,
       org_cparty.name cparty_name,
       fol.description fol_desc
  FROM organisations org,
       organisation_roles or1,
       organisations org_cparty,
       organisation_roles orr_cpy,
       currencies cur,
       contract_types ct,
       portfolios por,
       long_term_contracts ltc,
    --   long_term_schedules lts,
       cash_flow_types caft,
       fee_schedules fs,
       cash_flows cf,
       star_site_parameters ssp,
       folders fol
 WHERE 1 = 1
   AND ssp.current_site = 'Y'
   AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
 --  AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
   AND NOT cf.status IN ('5', '9')
   AND fs.fs_uid = cf.fs_uid
--   AND lts.lts_uid = fs.lts_uid
--   AND ltc.ltc_uid = lts.ltc_uid
   AND ltc.ltc_uid = fs.ltc_uid
   AND por.por_uid = ltc.por_uid
   AND ct.ct_uid = ltc.ct_uid
   AND caft.caft_uid = cf.caft_uid
   AND cur.cur_uid = cf.cur_uid
   AND or1.or_uid = ltc.or_uid_beneficiary
   AND org.org_uid = or1.org_uid
   AND orr_cpy.or_uid = ltc.or_uid_cparty
   AND org_cparty.org_uid = orr_cpy.org_uid
   AND por.portfolio_type = 'A'
   AND ltc.details_complete = DECODE(:details_complete,
                                     'Y', :details_complete,
                                     ltc.details_complete)
   AND por.contingent = DECODE(:contingent_liabilities,
                               'Y', por.contingent,
                               'N')
  AND ltc.domestic = DECODE(:domestic,
                               'Y', 'Y',
                               'N', 'N',
                               ltc.domestic) 
  AND ltc.fol_uid = fol.fol_uid(+) 
  AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date))  <= :exclude_if_contr_after )
UNION/* Repo Contract Fees */
SELECT por.description portfolio,
       TO_CHAR(cf.value_date, :default_date_format) value_date,
       cf.value_date value_date_date,
       ct.description type,
       cur.code ccy,
       cf.cur_uid con_cur,
       caft.code_description cf_type,
       to_char(rc.rc_uid) contract_no,
       TO_CHAR(rc.maturity_date, :default_date_format) maturity,
       rc.title loan_title,
       NVL(cf.amount, 0) amount,
       NVL(cf.base_amount, 0) base_amount,
       DECODE(cf.status,
              '1', 'Unconfirmed',
              '2', 'Confirmed',
              'Changed') status,
       cf.cf_uid,
       org_cparty.name cparty_name,
       fol.description fol_desc
  FROM organisations org,
       organisation_roles or1,
       organisations org_cparty,
       organisation_roles or_cpy,
       currencies cur,
       contract_types ct,
       portfolios por,
       repo_contracts rc,
       cash_flow_types caft,
       fee_schedules fs,
       cash_flows cf,
       star_site_parameters ssp,
       folders fol
 WHERE 1 = 1 
   AND ssp.current_site = 'Y'
   AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
--  AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :end_date
   AND NOT cf.status IN ('5', '9')
   AND fs.fs_uid = cf.fs_uid
   AND rc.rc_uid = fs.rc_uid
   AND por.por_uid = rc.por_uid
   AND ct.ct_uid = rc.ct_uid
   AND caft.caft_uid = cf.caft_uid
   AND cur.cur_uid = cf.cur_uid
   AND or1.or_uid = rc.or_benif_uid
   AND org.org_uid = or1.org_uid
   AND or_cpy.or_uid = rc.or_cparty_uid
   AND org_cparty.org_uid = or_cpy.org_uid
   AND por.portfolio_type = 'A'
   AND por.contingent = DECODE(:contingent_liabilities,
                               'Y', por.contingent,
                               'N')
  AND nvl(:domestic,'N') != 'Y'
  AND rc.fol_uid = fol.fol_uid(+)
  AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :exclude_if_contr_after )